using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;
using DAOLayer;

namespace DAOLayer
{
    public class DocGiaDao
    {
        public DataTable LayDanhSach()
        {
            DataTable dt = new DataTable();
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Select * From Docgia";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);

            cn.Close();
            return dt;
        }

        public void Them(DocGiaDto dgDto)
        {
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Insert into Docgia(Tendocgia, CMND,Dienthoai, Diachi, Tinhtrang) values (?, ?, ?, ?, ?)" +
            "";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@Tendocgia", OleDbType.WChar);
            cmd.Parameters.Add("@CMND", OleDbType.WChar);
            cmd.Parameters.Add("@Dienthoai", OleDbType.WChar);
            cmd.Parameters.Add("@Diachi", OleDbType.WChar);
            cmd.Parameters.Add("@Tinhtrang", OleDbType.Boolean);

            cmd.Parameters["@Tendocgia"].Value = dgDto.TenDocGia;
            cmd.Parameters["@CMND"].Value = dgDto.CMND;
            cmd.Parameters["@Dienthoai"].Value = dgDto.DienThoai;
            cmd.Parameters["@Diachi"].Value = dgDto.DiaChi;
            cmd.Parameters["@Tinhtrang"].Value = dgDto.TinhTrang;

            cmd.ExecuteNonQuery();
            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            dgDto.MaDocGia = (int)cmd.ExecuteScalar();
            cn.Close();
        }

        public void Xoa(int maDg)
        {
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Delete From Docgia Where Madocgia = ?";

            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Madocgia", OleDbType.Integer);
            cmd.Parameters["@Madocgia"].Value = maDg;
            cmd.ExecuteNonQuery();

            cn.Close();
        }

        public void Sua(DocGiaDto dgDto)
        {
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Update Docgia Set Tendocgia = ?,CMND = ?,Dienthoai = ?, Diachi = ?, Tinhtrang = ?  Where Madocgia = ?";

            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Tendocgia", OleDbType.WChar);
            cmd.Parameters.Add("@CMND", OleDbType.WChar);
            cmd.Parameters.Add("@Dienthoai", OleDbType.WChar);
            cmd.Parameters.Add("@Diachi", OleDbType.WChar);
            cmd.Parameters.Add("@Tinhtrang", OleDbType.Boolean);
            cmd.Parameters.Add("@Madocgia", OleDbType.Integer);

            cmd.Parameters["@Tendocgia"].Value = dgDto.TenDocGia;
            cmd.Parameters["@CMND"].Value = dgDto.CMND;
            cmd.Parameters["@Dienthoai"].Value = dgDto.DienThoai;
            cmd.Parameters["@Diachi"].Value = dgDto.DiaChi;
            cmd.Parameters["@Tinhtrang"].Value = dgDto.TinhTrang;
            cmd.Parameters["@Madocgia"].Value = dgDto.MaDocGia;

            cmd.ExecuteNonQuery();
            cn.Close();
        }

        ////////////////////////////////////////////////////////////////////////////
        //Tim kiem 1 doc gia
        //////////////////////////////////////////////////////////////////////////
        //Tim kiem 1 khach hang
        public DocGiaDto TimKiem(int maDG)
        {
            DocGiaDto dgDto = null;
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From Docgia Where Madocgia = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Madocgia", OleDbType.Integer);
            cmd.Parameters["@Madocgia"].Value = maDG;
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                dgDto = new DocGiaDto();
                dgDto.MaDocGia = (int)dr["Madocgia"];
                dgDto.TenDocGia = (string)dr["Tendocgia"];
                dgDto.CMND = (string)dr["CMND"];
                dgDto.DienThoai = (string)dr["Dienthoai"];
                dgDto.DiaChi = (string)dr["Diachi"];
                dgDto.TinhTrang = (Boolean)dr["Tinhtrang"];
            }
            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return dgDto;
        }
    }
}
